package com.lirh.poi;

import com.lirh.okhttp.TestHttp;
import com.lirh.pojo.MultiTree;
import com.lirh.pojo.TreeNode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import sun.reflect.generics.tree.Tree;

import javax.swing.*;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

//import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.stereotype.Service;
//import org.springframework.transaction.annotation.Transactional;

public class PreTestReadXlsToNode {

    public Map map=new HashMap();
    public TreeNode firstNode;
    public  HashMap<String, TreeNode> nodeMapperMap = new HashMap<String, TreeNode>();

    //pre importExcel
    public void importExcel(String file,String sheetName) throws Exception {



        //1/获取文件路径
        //1.1从桌面获取文件
//        FileSystemView fsv = FileSystemView.getFileSystemView();
//        String desktop = fsv.getHomeDirectory().getPath();
        //   String filepath = desktop + "test.xls";
        //String filepath = "/Users/lirh/Desktop/test.xls";
        String filepath = file;
        //2.通过流获取本地文件
        FileInputStream fileInputStream = new FileInputStream(filepath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        //3.创建工作薄对象，并获取工作表1
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheet(sheetName);
        firstNode=newNode(sheet.getRow(2).getCell(2).getStringCellValue());
       // int rowCount = sheet.getLastRowNum() + 1;
        int rowCount =0;
        int virRowCount = sheet.getLastRowNum() + 1;
        for (int i = 0; i <virRowCount ; i++) {
            HSSFRow row = sheet.getRow(i);
            try {
                if (!(row.getCell(2) == null) || (row.getCell(2).getStringCellValue() == ""))
                    rowCount++;
            }catch (Exception e){

            }
        }


        System.out.println(rowCount);

        for (int i = 2; i < rowCount; i++) {
            HSSFRow row = sheet.getRow(i);
            if(isMergedRegion(sheet,i,2)) {
                TreeNode mergeTreeNode = newNode(getMergedRegionValue(sheet, i, 2));
                String mergedNodeNumStr = getMergedRegionValue(sheet, i, 0);
                nodeMapperMap.put(mergedNodeNumStr,mergeTreeNode);
            }else {
               // newNode(getCellValue(row.getCell(0)));
                nodeMapperMap.put(getCellValue(row.getCell(0)), newNode(getCellValue(row.getCell(2))));
            }

        }

        for (int i = 2; i < rowCount; i++) {

            HSSFRow row = sheet.getRow(i);
            TreeNode node=null;
            String nodeMessage="" ;
            String chilsNodeName="";

           // System.out.println("--------");
            for (int j = 2; j <=4; j++) {

                switch (j){
                    case 2:
                        if(isMergedRegion(sheet,i,2)) {
                            node=(TreeNode) map.get(getMergedRegionValue(sheet, i, 2));
                        }else {
                            node=(TreeNode) map.get(getCellValue(row.getCell(2)));
                        }
                     //   System.out.println("node:"+node+"...");
                        break;
                    case 3:
                      // nodeMessage=getCellValue(row.getCell(1));
                        if ((row.getCell(3)==null)||(row.getCell(3).getStringCellValue().equals(""))){
                            break;
                        }
                        nodeMessage=row.getCell(3).getStringCellValue();
                       // System.out.println("nodemessage:"+nodeMessage+"....");
                        break;
                    case 4:
                        if (row.getCell(4)==null||(row.getCell(4).getStringCellValue().equals(""))){
                            break;
                        }
                      //  chilsNodeName=getCellValue(row.getCell(j));
                        String abcd=row.getCell(4).getStringCellValue().split("节点")[1];
                        System.out.println(abcd);
                        // chilsNodeName=nodeMapperMap.;
                       // System.out.println("chilsNodeName:"+chilsNodeName+"....");
                       // node.getChilds().put(nodeMessage,(TreeNode) map.get(chilsNodeName));
                        node.getChilds().put(nodeMessage,nodeMapperMap.get(abcd));
                        break;
                    default:
                            break;
                }
            }

        }
        System.out.println("!!!!!!");


    }
    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {
        if (cell == null) return "";
        return cell.getStringCellValue();
    }
    /**
     * 合并单元格处理,获取合并行
     *
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }
    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
    public TreeNode  newNode(String name){
        TreeNode treeNode=null;
        if (!map.keySet().contains(name)){
            treeNode = new TreeNode(name, new HashMap<String, TreeNode>());
            map.put(name,treeNode);
        }else {
            treeNode=(TreeNode) map.get(name);
        }
        return treeNode;
    }
//用例生成并请求
    public void writeResultToXls2(List<String> listpath, String file, String pid, JTextArea jta) throws Exception{
        String filepath = file;
        //2.通过流获取本地文件
        FileInputStream fileInputStream = new FileInputStream(filepath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        //3.创建工作薄对象，并获取工作表1
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet resultSheet=null;

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            if (workbook.getSheetAt(i).getSheetName().contains("py测试用例")) {
                workbook.removeSheetAt(i);
            }
        }
        if (resultSheet==null){
            resultSheet = workbook.createSheet("py测试用例");
        }
        //设置格式
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置单元格宽度
        resultSheet.setColumnWidth(1,5000);
        resultSheet.setColumnWidth(2,10000);
        resultSheet.setColumnWidth(3,10000);
        resultSheet.setColumnWidth(5,8000);


        HSSFRow row0 = resultSheet.createRow(0);
        HSSFCell cell0_0 = row0.createCell(0);
        cell0_0.setCellValue("用例编号");
        cell0_0.setCellStyle(cellStyle2);
        HSSFCell cell0_1 = row0.createCell(1);
        cell0_1.setCellValue("用户问答");
        cell0_1.setCellStyle(cellStyle2);
        HSSFCell cell0_2 = row0.createCell(2);
        cell0_2.setCellValue("bot实际回复");
        cell0_2.setCellStyle(cellStyle2);
        HSSFCell cell0_3 = row0.createCell(3);
        cell0_3.setCellValue("预期回复");
        cell0_3.setCellStyle(cellStyle2);
        HSSFCell cell0_4 = row0.createCell(4);
        cell0_4.setCellValue("测试结果");
        cell0_4.setCellStyle(cellStyle2);
        HSSFCell cell0_5 = row0.createCell(5);
        cell0_5.setCellValue("sessionId");
        cell0_5.setCellStyle(cellStyle2);


        HSSFRow row=row0;
        //设置false格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        //设置true格式
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setWrapText(true);
        //设置newStart格式

        HSSFCellStyle cellStyle4 = workbook.createCellStyle();
        cellStyle4.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle4.setWrapText(true);
        for (int i = 0; i <listpath.size() ; i++) {
            String uuid = UUID.randomUUID().toString();
            //HSSFRow row = resultSheet.createRow(i + 1);
            //int num=i+1;
            //row.createCell(0).setCellValue("用例"+num);
            String s = listpath.get(i);
            String[] split = s.split("->");
            for (int j = 0; j <split.length ; j++) {
                if (j%2==0){
                     row = resultSheet.createRow(row.getRowNum()+1);
                    row.createCell(1).setCellValue(split[j]);
                    //System.out.println("1:"+MultiTree.SIGN_BEGIN);
                    //System.out.println("2:"+row.getCell(1).getStringCellValue());
                    if ( row.getCell(1).getStringCellValue().contains(MultiTree.SIGN_BEGIN)){
                        row.getCell(1).setCellStyle(cellStyle4);
                        int ab=i+1;
                        row.createCell(0).setCellValue("用例"+ab);
                    }else{
                        row.getCell(1).setCellStyle(cellStyle3);
                    }
                    String answer = "（出错了.）";
                    try{
                        answer = TestHttp.testAnswer(uuid, split[j],pid);
                    }catch (Exception e){

                    }
                    System.out.println(split[j]);
                    jta.append(split[j]+"\n");
                    System.out.println(answer);
                    jta.append(answer+"\n");
                    row.createCell(2).setCellValue(answer);
                    row.getCell(2).setCellStyle(cellStyle3);
                    row.createCell(5).setCellValue(uuid);
                    row.getCell(5).setCellStyle(cellStyle3);
                }else{
                    row.createCell(3).setCellValue(split[j]);
                    row.getCell(3).setCellStyle(cellStyle3);
                    if (row.getCell(3).getStringCellValue().equals(row.getCell(2).getStringCellValue())){
                        row.createCell(4).setCellValue("true");
                    }else{
                        row.createCell(4).setCellValue("false");
                        row.getCell(4).setCellStyle(cellStyle);
                    }
                }

            }

        }
        FileOutputStream fileOutputStream=null;
        try{
            fileOutputStream = new FileOutputStream(filepath);
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
        }catch (Exception e){

        }finally {
            if (fileOutputStream!=null){
                try {
                    fileOutputStream.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }

        }

    }
    //用例生成
    public void writeResultToXls2222(List<String> listpath,String file) throws Exception{

        String filepath = file;
        //2.通过流获取本地文件
        FileInputStream fileInputStream = new FileInputStream(filepath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        //3.创建工作薄对象，并获取工作表1
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet resultSheet=null;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            if (workbook.getSheetAt(i).getSheetName().contains("py测试用例")) {
                workbook.removeSheetAt(i);
            }
        }
        if (resultSheet==null){
            resultSheet = workbook.createSheet("py测试用例");
        }
        //设置格式
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置单元格宽度
        resultSheet.setColumnWidth(1,5000);
        resultSheet.setColumnWidth(2,10000);
        resultSheet.setColumnWidth(3,10000);
        resultSheet.setColumnWidth(5,8000);


        HSSFRow row0 = resultSheet.createRow(0);
        HSSFCell cell0_0 = row0.createCell(0);
        cell0_0.setCellValue("用例编号");
        cell0_0.setCellStyle(cellStyle2);
        HSSFCell cell0_1 = row0.createCell(1);
        cell0_1.setCellValue("用户问答");
        cell0_1.setCellStyle(cellStyle2);
        HSSFCell cell0_2 = row0.createCell(2);
        cell0_2.setCellValue("bot实际回复");
        cell0_2.setCellStyle(cellStyle2);
        HSSFCell cell0_3 = row0.createCell(3);
        cell0_3.setCellValue("预期回复");
        cell0_3.setCellStyle(cellStyle2);
        HSSFCell cell0_4 = row0.createCell(4);
        cell0_4.setCellValue("测试结果");
        cell0_4.setCellStyle(cellStyle2);
        HSSFCell cell0_5 = row0.createCell(5);
        cell0_5.setCellValue("sessionId");
        cell0_5.setCellStyle(cellStyle2);


        HSSFRow row=row0;
        //设置false格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        //设置true格式
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setWrapText(true);
        //设置newStart格式

        HSSFCellStyle cellStyle4 = workbook.createCellStyle();
        cellStyle4.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle4.setWrapText(true);
        for (int i = 0; i <listpath.size() ; i++) {
            String uuid = UUID.randomUUID().toString();
            //HSSFRow row = resultSheet.createRow(i + 1);
            //int num=i+1;
            //row.createCell(0).setCellValue("用例"+num);
            String s = listpath.get(i);
            String[] split = s.split("->");
            for (int j = 0; j <split.length ; j++) {
                if (j%2==0){
                    row = resultSheet.createRow(row.getRowNum()+1);
                    row.createCell(1).setCellValue(split[j]);
                    //System.out.println("1:"+MultiTree.SIGN_BEGIN);
                    //System.out.println("2:"+row.getCell(1).getStringCellValue());
                    if ( row.getCell(1).getStringCellValue().contains(MultiTree.SIGN_BEGIN)){
                        row.getCell(1).setCellStyle(cellStyle4);
                        int ab=i+1;
                        row.createCell(0).setCellValue("用例"+ab);
                    }else{
                        row.getCell(1).setCellStyle(cellStyle3);
                    }
//                    row.createCell(2).setCellValue(TestHttp.testAnswer(uuid, split[j]));
//                    row.getCell(2).setCellStyle(cellStyle3);
//                    row.createCell(5).setCellValue(uuid);
//                    row.getCell(5).setCellStyle(cellStyle3);
                }else{
                    row.createCell(3).setCellValue(split[j]);
                    row.getCell(3).setCellStyle(cellStyle3);
//                    if (row.getCell(3).getStringCellValue().equals(row.getCell(2).getStringCellValue())){
//                        row.createCell(4).setCellValue("true");
//                    }else{
//                        row.createCell(4).setCellValue("false");
//                        row.getCell(4).setCellStyle(cellStyle);
//                    }
                }

            }

        }
        FileOutputStream fileOutputStream=null;
        try{
            fileOutputStream = new FileOutputStream(filepath);
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
        }catch (Exception e){

        }finally {
            if (fileOutputStream!=null){
                try {
                    fileOutputStream.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }

        }

    }
    //重新请求
    public void writeResultToXls11112(String file ,String pid,JTextArea jta) throws Exception{

        String filepath = file;
        //2.通过流获取本地文件
        FileInputStream fileInputStream = new FileInputStream(filepath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        //3.创建工作薄对象，并获取工作表1
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet resultSheet=null;

        resultSheet = workbook.getSheet("py测试用例");

        //设置格式
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置单元格宽度
        resultSheet.setColumnWidth(1,5000);
        resultSheet.setColumnWidth(2,10000);
        resultSheet.setColumnWidth(3,10000);
        resultSheet.setColumnWidth(5,8000);

        //设置false格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        //设置true格式
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setWrapText(true);
        HSSFCellStyle cellStyle4 = workbook.createCellStyle();
        cellStyle4.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        cellStyle4.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle4.setWrapText(true);

        String uuid = "uuid异常";
        for (int i = 1; i <resultSheet.getLastRowNum(); i++) {
            HSSFRow row = resultSheet.getRow(i);
            if (row.getCell(1)==null||row.getCell(1).getStringCellValue()==null||row.getCell(1).getStringCellValue().equals("")){
                break;
            }
            if (row.getCell(1).getStringCellValue().contains("newCall")){
                uuid = UUID.randomUUID().toString();
            }
            String answer = "（出错了.）";
            try{
                answer = TestHttp.testAnswer(uuid, row.getCell(1).getStringCellValue(),pid);
            }catch (Exception e){

            }
            System.out.println(row.getCell(1).getStringCellValue());
            jta.append(row.getCell(1).getStringCellValue()+"\n");
            System.out.println(answer);
            jta.append(answer+"\n");
            row.getCell(2).setCellValue(answer);
            row.getCell(5).setCellValue(uuid);
            if (row.getCell(3).getStringCellValue().equals(row.getCell(2).getStringCellValue())){
                row.getCell(4).setCellValue("true");
                row.getCell(4).setCellStyle(cellStyle3);

            }else{
                row.getCell(4).setCellValue("false");
                row.getCell(4).setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOutputStream=null;
        try{
            fileOutputStream = new FileOutputStream(filepath);
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
        }catch (Exception e){

        }finally {
            if (fileOutputStream!=null){
                try {
                    fileOutputStream.close();
                }catch (IOException e){
                    e.printStackTrace();
                }
            }

        }

    }

}


